﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;

namespace syc.Fc
{
    public partial class JinDu4_Report : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected int[] Data = new int[13];


        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                RiQi.Text = DateTime.Today.AddDays(-1).ToString("yyyy年M月d日");
            }
            this.QueryData();
        }

        protected void WriteToFile()
        {
            DateTime d;
            if(!DateTime.TryParse(this.RiQi.Text,out d))
            {
                d=DateTime.Today;
            }
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("四分厂日进度[" + d.ToLongDateString()) + "].xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("报表/模板/四分厂生产进度日报表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "四分厂生产进度日报表";
            hssfworkbook.SummaryInformation = si;
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {

            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("四分厂日报表");
            sheet1.GetRow(1).GetCell(0).SetCellValue(this.RiQi.Text);

            this.QueryData();

            sheet1.GetRow(3).GetCell(2).SetCellValue(Data[0]);
            sheet1.GetRow(3).GetCell(3).SetCellValue(Data[1]);
            sheet1.GetRow(3).GetCell(4).SetCellValue(Data[2]);
            sheet1.GetRow(3).GetCell(5).SetCellValue(Data[3]);

            sheet1.GetRow(4).GetCell(2).SetCellValue(Data[5]);
            sheet1.GetRow(4).GetCell(3).SetCellValue(Data[6]);
            sheet1.GetRow(4).GetCell(4).SetCellValue(Data[7]);
            sheet1.GetRow(4).GetCell(5).SetCellValue(Data[8]);

            sheet1.GetRow(8).GetCell(3).SetCellValue(Data[4]);
            sheet1.GetRow(8).GetCell(4).SetCellValue(Data[9]);
            sheet1.GetRow(8).GetCell(5).SetCellValue(Data[12]);

            sheet1.GetRow(6).GetCell(5).SetCellValue(Data[10]);
            sheet1.GetRow(7).GetCell(5).SetCellValue(Data[11]);

            string op = (string)this.Session["OperatorName"];
            if (op == null)
            {
                op = "";
            }
            sheet1.GetRow(9).GetCell(5).SetCellValue(op);
            sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

        private void QueryData()
        {
            DateTime d1, d2, d3;
            if (!DateTime.TryParse(RiQi.Text, out d1))
            {
                return;
            }
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;

            Cmd.CommandText =
@"declare @y int
declare @d1 smalldatetime
if exists(select * from 生产月设置 where 分厂=@fc and @d0>=起始日期 and @d0<=截止日期)
begin
	select @y=年度,@d1=起始日期 from 生产月设置 where 分厂=@fc and @d0>=起始日期 and @d0<=截止日期
	if exists(select * from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1)
	begin
		select @d1 as 月累计日期,起始日期 as 年累计日期 from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1
	end
end";
            Cmd.CommandText = Cmd.CommandText.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "4");
            Cmd.CommandType = CommandType.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d2 = (DateTime)Reader["月累计日期"];
                d3 = (DateTime)Reader["年累计日期"];
                this.RiQi2.Text = d2.ToLongDateString();
                this.RiQi3.Text = d3.ToLongDateString();
            }
            else
            {
                this.RiQi2.Text = d1.Year.ToString() + "年" + d1.Month.ToString() + "月1日";
                this.RiQi3.Text = d1.Year.ToString() + "年1月1日";
                d2 = DateTime.Parse(this.RiQi2.Text);
                d3 = DateTime.Parse(this.RiQi3.Text);
            }
            Reader.Close();

            string sql=@"select * from 
(select isnull(SUM(车丝1),0) as 车丝1,isnull(SUM(车丝2),0) as 车丝2,isnull(SUM(车丝3),0) as 车丝3,isnull(SUM(车丝4),0) as 车丝4,isnull(SUM(入库),0) as 入库 from 生产进度4 where 日期=@d1) t1,
(select isnull(SUM(车丝1),0) as 车丝1累计,isnull(SUM(车丝2),0) as 车丝2累计,isnull(SUM(车丝3),0) as 车丝3累计,isnull(SUM(车丝4),0) as 车丝4累计,isnull(SUM(入库),0) as 入库累计 from 生产进度4 where 日期>=@d2 and 日期<=@d1) t2,
(select isnull(SUM(车丝1),0) +  isnull(SUM(车丝2),0) as 内套年累计,isnull(SUM(车丝3),0) +  isnull(SUM(车丝4),0) as 外套年累计, isnull(SUM(入库),0) as 入库年累计 from 生产进度4 where 日期>=@d3 and 日期<=@d1)t3";

            sql = sql.Replace("@d1", "'"+d1.ToShortDateString()+"'");
            sql = sql.Replace("@d2", "'" + d2.ToShortDateString() + "'");
            sql = sql.Replace("@d3", "'" + d3.ToShortDateString() + "'");

            Cmd.CommandText = sql;

            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                for (int i = 0; i < 13; i++)
                {
                    Data[i] = (int)Reader[i];
                }
            }
            Reader.Close();

            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }
    }
}
